﻿using System;
using System.Collections.Generic;
using System.Configuration;
using Be;
using System.Data.SqlClient;

namespace Dalc {

    /// <summary>
    /// The DalcBooking class is reposible
    /// for dataaccess for bookings
    /// </summary>
    public class DalcBooking {

        /// <summary>
        /// The connectionstring
        /// </summary>
        private readonly string _connect;

        /// <summary>
        /// Construct the object and set the connectionstring
        /// </summary>
        public DalcBooking() {
            //_connect = @"Data Source=DCENT\SQLEXPRESS;Initial Catalog=DIBS;Integrated Security=True";
            _connect = @"Data Source=KENNETH-PC\SQLEXPRESS;Initial Catalog=DIBS;Integrated Security=True";
        }

        /// <summary>
        /// Get all bookings
        /// </summary>
        /// <returns>All bookings</returns>
        public List<BeBooking> GetBookings() {
            SqlConnection con = new SqlConnection(_connect);
            List<BeBooking> bookings = new List<BeBooking>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Booking";// WHERE StartTime >= GETDATE() ORDER BY StartTime ASC";
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    BeBooking booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                    bookings.Add(booking);
                }
                return bookings;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get all the bookings on a specific day
        /// </summary>
        /// <param name="day">The day to get the bookings from</param>
        /// <returns>The bookings</returns>
        /*public List<BeBooking> GetBookingsOnDate(DateTime day) {
            SqlConnection con = new SqlConnection(_connect);
            List<BeBooking> bookings = new List<BeBooking>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Booking WHERE CONVERT(Date, StartTime) = @date";
                cmd.Parameters.AddWithValue("@date", day.Date);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    BeBooking booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                    bookings.Add(booking);
                }
                return bookings;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }*/

        /// <summary>
        /// Get a booking from the id
        /// </summary>
        /// <param name="id">The id to get the booking from</param>
        /// <returns>The booking if the id is found. Null otherwise</returns>
        public BeBooking GetBookingFromId(int id) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "SELECT * FROM Booking WHERE Id = @id";
                cmd.Parameters.AddWithValue("@id", id);
                BeBooking booking = null;
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                }
                return booking;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get all bookings in a specific lesson
        /// </summary>
        /// <param name="lesson">The lesson to get the bookings from</param>
        /// <returns>The bookings</returns>
        public List<BeBooking> GetBookingsOnLesson(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            List<BeBooking> bookings = new List<BeBooking>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Booking Where LessonId = @lessonId";
                cmd.Parameters.AddWithValue("@lessonId", lesson.Id);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    BeBooking booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                    bookings.Add(booking);
                }
                return bookings;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get all bookings in a specific week
        /// </summary>
        /// <param name="startDay">The start day</param>
        /// <param name="days">The days from starttimes</param>
        /// <returns>The bookings</returns>
        public List<BeBooking> GetEvents(DateTime startDay, int days) {
            SqlConnection con = new SqlConnection(_connect);
            List<BeBooking> bookings = new List<BeBooking>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT Booking.Id, Booking.StartTime, Booking.EndTime, Type.Name, Booking.UserId FROM Booking inner join Lesson on LessonId = Lesson.Id inner join Type on Lesson.TypeId = Type.Id WHERE NOT ((Booking.EndTime <= @start) OR (Booking.StartTime >= @end))";
                cmd.Parameters.AddWithValue("@start", startDay);
                cmd.Parameters.AddWithValue("@end", startDay.AddDays(days));
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    string userId = sdr["UserId"].ToString();
                    string type = sdr["Name"].ToString();
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    BeBooking booking = new BeBooking(id, starttime, endtime, userId, type);
                    bookings.Add(booking);
                }
                return bookings;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get the previous booking from the current booking
        /// </summary>
        /// <param name="currentBooking">The current booking</param>
        /// <returns>The previous booking if it is found. Null otherwise</returns>
        public BeBooking GetPreviousBooking(BeBooking currentBooking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "SELECT TOP 1 * FROM Booking WHERE EndTime <= @starttime ORDER BY EndTime DESC";
                cmd.Parameters.AddWithValue("@starttime", currentBooking.StartTime);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                BeBooking booking = null;
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                }
                return booking;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get the next booking from the current booking
        /// </summary>
        /// <param name="currentBooking">The current booking</param>
        /// <returns>The next booking if it is found. Null otherwise</returns>
        public BeBooking GetNextBooking(BeBooking currentBooking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "SELECT TOP 1 * FROM Booking WHERE StartTime >= @endtime ORDER BY StartTime ASC";
                cmd.Parameters.AddWithValue("@endtime", currentBooking.EndTime);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                BeBooking booking = null;
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    double startLat = (double) sdr["StartLatitude"];
                    double startLong = (double) sdr["StartLongitude"];
                    double endLat = (double) sdr["EndLatitude"];
                    double endLong = (double) sdr["EndLongitude"];
                    string userId = sdr["UserId"].ToString();
                    int lessonId = (int) sdr["LessonId"];
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    booking = new BeBooking(id, starttime, endtime, startLat, startLong, endLat, endLong, userId, lessonId);
                }
                return booking;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Save a booking
        /// </summary>
        /// <param name="booking">The booking to save</param>
        /// <returns>True if the booking is saved. False otherwise</returns>
        public bool SaveBooking(BeBooking booking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "INSERT INTO Booking (StartTime, EndTime, StartLatitude, StartLongitude, EndLatitude, EndLongitude, LessonId, UserId) VALUES (@starttime, @endtime, @startLatitude, @startLongitude, @endLatitude, @endLongitude, @lessonId, @userId)";
                cmd.Parameters.AddWithValue("@starttime", booking.StartTime);
                cmd.Parameters.AddWithValue("@endtime", booking.EndTime);
                cmd.Parameters.AddWithValue("@startLatitude", booking.StartLatitude);
                cmd.Parameters.AddWithValue("@startLongitude", booking.StartLongitude);
                cmd.Parameters.AddWithValue("@endLatitude", booking.EndLatitude);
                cmd.Parameters.AddWithValue("@endLongitude", booking.EndLongitude);
                cmd.Parameters.AddWithValue("@lessonId", booking.LessonId);
                cmd.Parameters.AddWithValue("@userId", booking.UserId);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Edit a booking
        /// </summary>
        /// <param name="booking">The booking to edit</param>
        /// <returns>True if the booking is edited. False otherwise</returns>
        public bool EditBooking(BeBooking booking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "UPDATE Booking SET StartTime=@starttime, EndTime=@endtime, StartLatitude=@startLatitude, StartLongitude=@startLongitude, EndLatitude=@endLatitude, EndLongitude=@endLongitude, LessonId=@lessonId, UserId=@userId WHERE Id=@bookingId";
                cmd.Parameters.AddWithValue("@starttime", booking.StartTime);
                cmd.Parameters.AddWithValue("@endtime", booking.EndTime);
                cmd.Parameters.AddWithValue("@startLatitude", booking.StartLatitude);
                cmd.Parameters.AddWithValue("@startLongitude", booking.StartLongitude);
                cmd.Parameters.AddWithValue("@endLatitude", booking.EndLatitude);
                cmd.Parameters.AddWithValue("@endLongitude", booking.EndLongitude);
                cmd.Parameters.AddWithValue("@lessonId", booking.LessonId);
                cmd.Parameters.AddWithValue("@userId", booking.UserId);
                cmd.Parameters.AddWithValue("@bookingId", booking.Id);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Delete a booking
        /// </summary>
        /// <param name="booking">The booking to delete</param>
        /// <returns>True if the booking is deleted. False otherwise</returns>
        public bool DeleteBooking(BeBooking booking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "DELETE FROM Booking WHERE Id = @id";
                cmd.Parameters.AddWithValue("@id", booking.Id);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Delete all bookings from a specific lesson
        /// </summary>
        /// <param name="lesson">The lesson to delete the bookings from</param>
        /// <returns>True if the bookings is deleted. False otherwise</returns>
        public bool DeleteBookingFromLesson(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "DELETE FROM Booking WHERE LessonId = @lessonId";
                cmd.Parameters.AddWithValue("@lessonId", lesson.Id);
                con.Open();
                cmd.ExecuteNonQuery();
                return true;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Check if the booking overlaps existing bookings
        /// </summary>
        /// <param name="booking">The booking to check</param>
        /// <returns>True if the booking overlaps. False otherwise</returns>
        public bool OverlapsBookings(BeBooking booking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "SELECT * FROM Booking WHERE StartTime < @endtime AND EndTime > @starttime AND UserId = @userId AND Id != @id";
                cmd.Parameters.AddWithValue("@endtime", booking.EndTime);
                cmd.Parameters.AddWithValue("@starttime", booking.StartTime);
                cmd.Parameters.AddWithValue("@userId", booking.UserId);
                cmd.Parameters.AddWithValue("@id", booking.Id);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                return sdr.HasRows;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }
    }
}